package cn.edu.scau.repository;

import cn.edu.scau.pojo.entity.EmployeeEntity;
import cn.edu.scau.pojo.vo.ChartVO;
import cn.edu.scau.repository.base.BaseRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.Temporal;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.persistence.TemporalType;
import java.util.Date;
import java.util.List;

/**
 * @description: 员工DAO
 * @author: yuxiang
 * @create: 2021-03-29 21:00
 **/
@Repository
public interface EmployeeRepository  extends BaseRepository<EmployeeEntity,Integer> {


    Long countAllByDateOfHireAfter(@Param("dateOfHire") Date time);

    /**
     * 新进人员 入职
     * @param startTime
     * @param endTime
     * @return
     */
    Long countAllByDateOfHireBetween(@Temporal(TemporalType.DATE)Date startTime,@Temporal(TemporalType.DATE) Date endTime);

    @Query(value= " SELECT COUNT(employeeen0_.id) FROM r_employee employeeen0_ WHERE employeeen0_.date_of_hire > ?1",nativeQuery = true)
    Long countThisYear(String time);

    @Query(value= " SELECT COUNT(employeeen0_.id) FROM r_employee employeeen0_ WHERE employeeen0_.date_of_termination is null ",nativeQuery = true)
    Long countAllByDateOfTerminationIsNull();

    /**
     * 人才流失情况
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "select new cn.edu.scau.pojo.vo.ChartVO(count(et.id),et.termReason ) from EmployeeEntity et where et.dateOfTermination is not null and et.dateOfTermination between :startTime and :endTime group by et.termReason")
    List<ChartVO> countYearTerm(@Param("startTime")@Temporal(TemporalType.DATE)Date startTime,@Param("endTime") @Temporal(TemporalType.DATE) Date endTime);

    Long countAllByDateOfTerminationIsNotNullAndAndDateOfTerminationBetween(@Temporal(TemporalType.DATE)Date startTime,@Temporal(TemporalType.DATE) Date endTime);

    /**
     * 计算平均工资
     * @return
     */
    @Query(value="SELECT AVG(e.salary) FROM EmployeeEntity e")
    Double avgSalaryAll();

    /**
     * 全部部门
     * SELECT employeeen0_.department AS col_1_0_ FROM r_employee employeeen0_ GROUP BY employeeen0_.department
     * @return
     */
    @Query(value="select et.department from EmployeeEntity et group by et.department")
    List<String> selectAllDepartment();

    /**
     * 查询部门人数入职人数
     * @param startTime
     * @param endTime
     * @param department
     * @return
     */
    Long countAllByDateOfHireBetweenAndDepartmentEquals(@Temporal(TemporalType.DATE)Date startTime,@Temporal(TemporalType.DATE) Date endTime,@Param("department")String department);

    /**
     * 招聘情况
     * @return
     */
    @Query(value = "select new cn.edu.scau.pojo.vo.ChartVO(count(et.id),et.recruitmentSource ) from EmployeeEntity et group by et.recruitmentSource")
    List<ChartVO> countRecruitSource();

    /**
     * 学历情况
     */
    @Query(value = "select new cn.edu.scau.pojo.vo.ChartVO(count(et.id),et.education ) from EmployeeEntity et group by et.education")
    List<ChartVO> countHrEducation();

    /**
     * 计算工资总数
     * @return
     */
    @Query(value="select sum(et.salary) from  EmployeeEntity et ")
    Long sumSalary();

    /**
     * 查询部门工资
     * @return
     */
    @Query(value="SELECT AVG(e.salary) FROM EmployeeEntity e where e.department = :department")
    Double avgSalaryByDepartment(@Param("department")String department);

    /**
     * 统计在职部门性别
     * @param department
     * @param sex
     * @return
     */
    Long countAllByDepartmentAndSexAndDateOfTerminationIsNull(@Param("department")String department,@Param("sex")String sex);

    /**
     * 部门-学历情况
     */
    @Query(value = "select new cn.edu.scau.pojo.vo.ChartVO(count(et.id),et.education ) from EmployeeEntity et where et.department = :department group by et.education")
    List<ChartVO> countHrEducationByDepartment(@Param("department")String department);

    /**
     * 全部学历
     * @return
     */
    @Query(value="select et.education from EmployeeEntity et group by et.education")
    List<String> selectAllEducation();

    /**
     * 计算不同学历平均工资
     * @param edu
     * @return
     */
    @Query(value="SELECT AVG(e.salary) FROM EmployeeEntity e where e.education = :edu")
    Double avgSalaryByEducation(@Param("edu")String edu);

    /**
     * 计算不同部门绩效
     * @param dep
     * @return
     */
    @Query(value="SELECT AVG(e.perfScoreID) FROM EmployeeEntity e where e.department = :dep and e.dateOfTermination is null ")
    Double avgPerfByDept(@Param("dep")String dep);

    /**
     * 计算部门人数
     * @return
     */
    Long countAllByDateOfHireAfterAndDateOfTerminationIsNotNullAndDepartmentEquals(@Param("dateOfHire")@Temporal(TemporalType.DATE)Date startTime,@Param("department")String department);
}
